﻿//**************************
//开发人员：Morning Sun QQ:576830428  微信:MorningSun0125
//业务范围：网站开发、小程序开发、web应用开发,桌面程序开发,工控类软件开发等
//开发日期：2016年1月10日
//版本号：Version 3.0 CopyRight 2011-2029
//**************************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace FileServer
{

    [Serializable]
    public class TbClient
    {
        #region 成员变量和公共属性
        /// <summary>
        /// 模型属性（ColString）
        /// </summary>

        private string _colString;
        public string ColString
        {
            set { _colString = value; }
            get { return _colString; }
        }

        /// <summary>
        /// 模型属性（WhereString）
        /// </summary>

        private string _whereString;
        public string WhereString
        {
            set { _whereString = value; }
            get { return _whereString; }
        }

        /// <summary>
        /// 模型属性（OrderString）
        /// </summary>

        private string _orderString;
        public string OrderString
        {
            set { _orderString = value; }
            get { return _orderString; }
        }

        /// <summary>
        /// 模型属性（）
        /// </summary>

        private string _ClientDir;
        public string ClientDir
        {
            set { _ClientDir = value; }
            get { return _ClientDir; }
        }

        /// <summary>
        /// 模型属性（）
        /// </summary>

        private string _ClientIP;
        public string ClientIP
        {
            set { _ClientIP = value; }
            get { return _ClientIP; }
        }

        /// <summary>
        /// 模型属性（）
        /// </summary>

        private int? _ID;
        public int? ID
        {
            set { _ID = value; }
            get { return _ID; }
        }

        #endregion
    }


    public class DalTbClient
    {
        public DalTbClient() { }
        //数据库名称
        private string dbname;
        public string DbName
        {
            get { return dbname; }
            set { dbname = value; }
        }
        #region  成员方法


        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name='model'>TbClient模型实例</param>
        /// <returns>返回大于0表示成功，否则失败</returns>

        public int AddByModel(TbClient model)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlCol = new StringBuilder();
            StringBuilder strSqlVal = new StringBuilder();

            List<OleDbParameter> listParm = new List<OleDbParameter>();
            if (model.ClientDir != null)
            {
                strSqlCol.Append("ClientDir,");
                strSqlVal.Append("@ClientDir,");
                OleDbParameter parms = new OleDbParameter("@ClientDir", OleDbType.VarChar, 50);
                parms.Value = model.ClientDir;
                listParm.Add(parms);
            }
            if (model.ClientIP != null)
            {
                strSqlCol.Append("ClientIP,");
                strSqlVal.Append("@ClientIP,");
                OleDbParameter parms = new OleDbParameter("@ClientIP", OleDbType.VarChar, 50);
                parms.Value = model.ClientIP;
                listParm.Add(parms);
            }
            strSql.Append("insert into TbClient( ");
            strSql.Append(strSqlCol.ToString().Remove(strSqlCol.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSqlVal.ToString().Remove(strSqlVal.Length - 1));
            strSql.Append(")");
            OleDbParameter[] parameters = new OleDbParameter[listParm.Count];
            listParm.CopyTo(parameters); ;
            int obj = DbHelperOledb.ExecuteSql(strSql.ToString(), parameters);
            return obj;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name='sqlStr'>插入信息sql语句</param>
        /// <returns>返回大于0表示成功，否则失败</returns>

        public int AddBySql(string sqlStr)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(sqlStr);
            return DbHelperOledb.ExecuteSql(strSql.ToString());
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name='model'>TbClient模型实例</param>
        /// <returns>返回大于0表示成功，否则失败</returns>

        public int UpDateByModel(TbClient model)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlCol = new StringBuilder();
            List<OleDbParameter> listParm = new List<OleDbParameter>();

            strSql.Append("update TbClient set ");
            if (model.ClientDir != null)
            {
                strSqlCol.Append("ClientDir=@ClientDir,");
                OleDbParameter parms = new OleDbParameter("@ClientDir", OleDbType.VarChar, 50);
                parms.Value = model.ClientDir;
                listParm.Add(parms);
            }
            if (model.ClientIP != null)
            {
                strSqlCol.Append("ClientIP=@ClientIP,");
                OleDbParameter parms = new OleDbParameter("@ClientIP", OleDbType.VarChar, 50);
                parms.Value = model.ClientIP;
                listParm.Add(parms);
            }

            OleDbParameter parmskey = new OleDbParameter("@ID", OleDbType.Integer);
            parmskey.Value = model.ID;


            strSql.Append(strSqlCol.ToString().Remove(strSqlCol.Length - 1));
            strSql.Append(" where ID=@ID ");
            listParm.Add(parmskey);

            OleDbParameter[] parameters = new OleDbParameter[listParm.Count];
            listParm.CopyTo(parameters);
            return DbHelperOledb.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name='sqlStr'>更新信息的sql语句</param>
        /// <returns>返回大于0表示成功，否则失败</returns>

        public int UpDateBySql(string sqlStr)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(sqlStr);
            return DbHelperOledb.ExecuteSql(strSql.ToString());
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name='xx'>表主键</param>
        /// <returns>返回大于0表示成功，否则失败</returns>

        public int DeleteByPreKey(int ID)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TbClient ");
            strSql.Append(" where ID=@ID");
            OleDbParameter[] parameters = { new OleDbParameter("@ID", OleDbType.Integer, 4) };
            parameters[0].Value = ID;
            return DbHelperOledb.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name='sqlStr'>删除信息sql语句</param>
        /// <returns>返回大于0表示成功，否则失败</returns>

        public int DeleteBySql(string sqlStr)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(sqlStr);
            return DbHelperOledb.ExecuteSql(strSql.ToString());
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name='xx'>表主键</param>
        /// <returns>返回大于0表示成功，否则失败</returns>

        public TbClient GetModel(int id)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ClientDir, ClientIP, ID from  TbClient");
            strSql.Append(" where ID=@ID ");
            OleDbParameter[] parameters = { 
		new OleDbParameter("@ID", OleDbType.Integer,4 )
 		};
            parameters[0].Value = id;
            TbClient model = new TbClient();
            model.ID = id;
            DataSet ds = DbHelperOledb.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.ClientDir = ds.Tables[0].Rows[0]["ClientDir"].ToString();
                model.ClientIP = ds.Tables[0].Rows[0]["ClientIP"].ToString();
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name='strWhere'>除where关键字以为的条件sql语句</param>
        /// <returns>DataSet</returns>

        public DataSet GetListByWhere(string strWhere)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ClientDir, ClientIP, ID");
            strSql.Append(" FROM TbClient ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOledb.Query(strSql.ToString());
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name='strSql'>sql语句</param>
        /// <returns>DataSet</returns>

        public DataSet GetListBySql(string strSql)
        {
            SetDataBase();
            StringBuilder strsql = new StringBuilder();
            strsql.Append(strSql);
            return DbHelperOledb.Query(strsql.ToString());
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name='model'>sql模型</param>
        /// <returns>DataSet</returns>

        public DataSet GetListByModel(TbClient model)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            if (model.ColString != null && model.ColString != "")
            {
                strSql.Append("select  " + model.ColString);
            }
            else
            {
                strSql.Append("select  * ");
            }
            strSql.Append("  from  TbClient  ");
            if (model.WhereString != null && model.WhereString != "")
            {
                strSql.Append("  where  " + model.WhereString);
            }
            if (model.OrderString != null && model.OrderString != "")
            {
                strSql.Append("   " + model.OrderString);
            }
            return DbHelperOledb.Query(strSql.ToString());
        }


        /// <summary>
        /// 获得所有数据列表
        /// </summary>
        /// <returns>DataSet</returns>

        public DataSet GetAllList()
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ClientDir, ClientIP, ID");
            strSql.Append(" FROM TbClient ");
            return DbHelperOledb.Query(strSql.ToString());
        }


        /// <summary>
        /// 查询前几条数据列表
        /// </summary>
        /// <param name='Top'>条数</param>
        /// <param name='strWhere'>除where关键字的条件sql语句</param>
        /// <param name='filedOrder'>排序或者分组sql语句</param>
        /// <returns>DataSet</returns>

        public DataSet GetTopList(int Top, string strWhere, string filedOrder)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top  " + Top + " ClientDir, ClientIP, ID");
            strSql.Append(" FROM TbClient ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (filedOrder.Trim() != "")
            {
                strSql.Append(filedOrder);
            }
            return DbHelperOledb.Query(strSql.ToString());
        }


        /// <summary>
        /// 获得数据记录数
        /// </summary>
        /// <param name='strWhere'>除where关键字以外的条件sql语句</param>
        /// <returns>总记录数</returns>

        public int GetCount(string strWhere)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  count(*) as cnum   ");
            strSql.Append(" FROM TbClient ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = new DataSet();
            ds = DbHelperOledb.Query(strSql.ToString());
            int result = Convert.ToInt32(ds.Tables[0].Rows[0]["cnum"].ToString());
            return result;
        }


        /// <summary>
        /// 获取翻页的数据列表
        /// </summary>
        /// <param name='colString'>列索引</param>
        /// <param name='strWhere'>除where关键字以外的条件sql语句</param>
        /// <param name='strOrder'>排序的条件sql语句</param>
        /// <param name='pageIndex'>当前页数</param>
        /// <param name='pageSize'>每页记录数</param>
        /// <param name='recordcount'>返回总记录数</param>
        /// <param name='table'>存放的表名</param>
        /// <returns>DataSet</returns>

        public DataSet GetPageByWhere(string colString, string strWhere, string strOrder, int pageindex, int pagesize, out int recordcount, string table)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            if (colString.Trim() != "")
            {
                strSql.Append("select  " + colString);
            }
            else
            {
                strSql.Append("select  *   ");
            }
            strSql.Append("  FROM  TbClient ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                recordcount = GetCount(strWhere);
            }
            else
            {
                recordcount = GetCount("");
            }
            if (strOrder.Trim() != "")
            {
                strSql.Append(strOrder);
            }
            return DbHelperOledb.PagedataSet(strSql.ToString(), pageindex, pagesize, table);
        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name='xx'>表主键</param>
        /// <returns>返回大于true表示存在，false表示不存在</returns>

        public bool ExistsByPreKey(int ID)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TbClient  ");
            strSql.Append(" FROM TbClient where ID=@ID");
            OleDbParameter[] parameters = { new OleDbParameter("@ID", OleDbType.Integer, 4) };
            parameters[0].Value = ID;
            return DbHelperOledb.Exists(strSql.ToString());
        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name='sqlStr'>查询sql语句</param>
        /// <returns>返回true表示存在，false表示不存在</returns>

        public bool ExistsBySql(string sqlStr)
        {
            SetDataBase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(sqlStr);
            return DbHelperOledb.Exists(strSql.ToString());
        }


        /// <summary>
        ///设置数据库
        /// </summary>
        private void SetDataBase()
        {
            if (dbname != null && dbname != "")
            {
                FileServer.DbHelperOledb.SetDataBase(dbname);
            }
            else
            {
                FileServer.DbHelperOledb.SetDataBase("");
            }
        }
        #endregion  成员方法
    }


}
